package com.xiaominge.utils.excleUtils.excelInput;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Map.Entry;


/**
 * @program: parent
 * @description: 读取excle内容
 * @author: xiaominge
 * @create: 2019-07-31 16:04
 **/

public class ExcelInputUtils {

    static DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    /**
     * 阅读列表地图
     *
     * @param inputStream 文件的输入 输入流
     * @param columns     列属性名     顺序必须和excel 列名称顺序一样
     * @return  读取出来的值 都处理为string 类型
     * @throws IOException ioexception
     */
    public static List<Map<String, String>> readToListMap(InputStream inputStream, String... columns) throws IOException {
        Workbook wb;
        Sheet sheet;
        Row row;
        List<Map<String, String>> list = null;
        String cellData;
        wb = WorkbookFactory.create(inputStream);
        if (wb != null) {
            //用来存放表中数据
            list = new ArrayList<>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i < rownum; i++) {
                Map<String, String> map = new LinkedHashMap<String, String>();
                row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < colnum; j++) {
                        cellData = getCellValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                } else {
                    break;
                }
                list.add(map);
            }
        }
        return list;
    }

    /**
     * 根据excel单元格类型 获取 单元格值
     *
     * @param cell
     */
    public static String getCellValue(Cell cell) {
        String cellValue ;
        switch (cell.getCellType()) {

            case NUMERIC:
                //判断cell是否为日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    //转换为日期格式YYYY-mm-dd
                    Date d = cell.getDateCellValue();
                    cellValue = dateTimeFormatter.format(LocalDateTime.ofInstant(d.toInstant(), ZoneId.systemDefault()));
                } else {
                    double numericCellValue = cell.getNumericCellValue();
                    BigDecimal bdVal = new BigDecimal(numericCellValue);
                    if ((bdVal + ".0").equals(Double.toString(numericCellValue))) {
                        // 整型
                        cellValue = bdVal.toString();
                    } else if (String.valueOf(numericCellValue).contains("E10")) {
                        // 科学记数法
                        cellValue = new BigDecimal(numericCellValue).toPlainString();
                    } else {
                        // 浮点型
                        cellValue = String.valueOf(numericCellValue);
                    }
                }
                break;
            case STRING:
                cellValue = cell.getRichStringCellValue().getString();
                break;
            case BOOLEAN:
                cellValue=String.valueOf( cell.getBooleanCellValue());
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

}
